Logo

Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.

Database Events

Database Events, added in VFP 7, are a cool enhancement to an already powerful data engine. Like a trigger, which automatically executes procedural code when a record is inserted, updated, or deleted, a database event automatically executes a specific routine when something is done to a member of the DBC (such as a table or view) or to the DBC itself. These events always fire, whether you do something visually (for example, selecting a view in the Database Designer and pressing the Delete key) or programmatically (executing the DROP VIEW command), in a runtime or development environment, through VFP code or through the OLE DB provider. Events are available for almost any action you can take on any object in a DBC, such as opening or closing a table or view, creating a connection, dropping a relation, validating the DBC, and so forth.

By default, Database Events are turned off. There are two ways you can turn them on for a given DBC. You can do it programmatically for the current database by issuing something like:

DBSETPROP("MyDatabase", "Database", "DBCEvents", .T.)

You can also do it visually using the Set Events On check box in the Database Properties dialog (choose Properties from the Database menu or right-click in the Database Designer and choose Properties from the shortcut MENU TO display this dialog).

The Version property of a DBC returned by DBGetProp(), normally 10, is 11 if Database Events are turned on.

A DBC (and therefore anything in the database) with events turned on cannot be accessed by previous versions of VFP or through the VFP ODBC driver. Of course, you can always turn events back off again, but only from within VFP 7. If you want to maintain compatibility with older applications but use Database Events in your new versions, have your new applications explicitly turn Database Events on when they start. Just be aware that while the new applications are running, the older versions won’t be able to access the database.

Turning Database Events on doesn’t mean anything will happen; you have to specify what events you want to handle by creating routines with the names VFP expects for these events. The procedure names all begin with “DBC_”, followed by the name of the event (for example, DBC_BeforeOpenTable is the name of the procedure VFP executes when a table is opened). You can use the ALANGUAGE() function to fill an array with the names of Database Events.

Database event code can be placed in the stored procedures of a DBC or in a separate PRG file. There are two ways you can specify a separate PRG file. Programmatically, use something like the following (where lcPRGName contains the name of an existing PRG):

DBSETPROP("MyDatabase", "Database", "DBCEventFileName", "MyProcs.PRG")

The other way is to use the Events File check box and associated Open File button in the Database Properties dialog. Doing it visually doesn’t automatically create the PRG; you must select an existing file, even if it’s empty.

Although you can type the database event names and parameter lists yourself, VFP can generate “template” code for you. Bring up the Database Properties dialog and select the events you want to handle in the Events list box (any events that already have code will appear in bold) and then click the Edit Code button. An edit window for either the stored procedures of the DBC or the separate PRG file, depending on which you’re using, appears with template code for each of the events you selected. All you have to do is fill in the code to execute for each event.

If you want to work on existing code for a single event or generate the template for it, double-clicking on the event will do the trick.

Hold down the Ctrl and Shift keys while clicking the Edit Code button to generate template code with a ? statement to display the event name and parameters passed (although we would have preferred a DEBUGOUT statement instead).

Most actions have both “Before” and “After” events; in the case of actions that have only a single event, that event is implicitly a Before event. While the After events just receive notification that an action took place, the Before events can actually prevent the action from occurring by returning False. For example, if DBC_BeforeOpenTable returns .F. unconditionally, you won’t be able to open any tables or views. Obviously, you want to be careful how you handle these events or you can make your database unusable!

Database Events are available for the DBC itself, as well as for tables, views, relations, and connections.

The stored procedures of a database are like a PRG file in that if there are two or more procedures with the same name, the last one is the one executed when the procedure is called. While this can be handy for testing—no need to comment out a procedure to insert test code when you can just create another procedure below the "real" one, using the same name, containing the test code—it can cause you to pull your hair out trying to figure out why the code you've written isn't executed and some other code is run instead.

Database Events for Databases

Almost everything you can do with a database has a DBC event associated with it; the only one missing is a CompileDatabase event.

Event

Fired When

Activate

The database becomes the current one.

AfterAppendProc

After procedures are appended.

AfterCopyProc

After procedures are copied.

AfterDBGetProp

After DBGETPROP() is called.

AfterDBSetProp

After DBSETPROP() is called.

AfterModifyProc

After stored procedures are modified.

AfterValidateData

After VALIDATE DATABASE is called.

BeforeAppendProc

Before stored procedures are appended.

BeforeCopyProc

Before stored procedures are copied.

BeforeDBGetProp

Before DBGETPROP() is called.

BeforeDBSetProp

Before DBSETPROP() is called.

BeforeModifyProc

Before stored procedures are modified.

BeforeValidateData

Before VALIDATE DATABASE is called.

CloseData

The database is closed.

Deactivate

The database is no longer the current one.

ModifyData

The Database Designer is displayed.

OpenData

The database is opened.

PackData

The database is packed.

Database Events for Tables

There’s a database event associated with everything you can do structurally with a table. One thing is a little goofy: The REMOVE TABLE and DROP TABLE commands, which do the same thing, have a different set of events. If you want to trap the removal of a table, you have to be sure to handle both sets of events. (The trick to this, of course, is to designate one stored procedure you create to hold the logic for both events, and have each of the events call that stored procedure. Calling code in an event is poor OOP.) Another issue is that if you open a table with a different alias, that alias (rather than the name of the table) is passed for the table name parameter. The workaround is to use CURSORGETPROP(“SourceName”) to determine the real name of the table.

Event

Fired When

AfterAddTable

After a free table is added to the database.

AfterCloseTable

After a table is closed.

AfterCreateTable

After a table is created.

AfterDropTable

After a table is removed with the DROP TABLE command.

AfterModifyTable

After a table's structure is modified.

AfterOpenTable

After a table is opened.

AfterRemoveTable

After a table is removed with the REMOVE TABLE command or interactively.

AfterRenameTable

After a table's name (but not DBF name) has been changed.

BeforeAddTable

Before a free table is added to the database.

BeforeCloseTable

Before a table is closed.

BeforeCreateTable

Before a table is created.

BeforeDropTable

Before a table is removed with the DROP TABLE command.

BeforeModifyTable

Before a table's structure is modified.

BeforeOpenTable

Before a table is opened.

BeforeRemoveTable

Before a table is removed with the REMOVE TABLE command or interactively.

BeforeRenameTable

Before a table's name (but not DBF name) is changed.

Database Events for Views

As with tables, anything you can do with a view has a database event associated with it. Some actions with views, such as creating or opening, cause the source tables to be opened, so the BeforeOpenTable and AfterOpenTable events fire for those tables as well, which both makes sense and is desirable. Interestingly, these events fire even if the tables are already open, giving us a glimpse of some internal processes involved in opening the view (such as opening a second copy of the tables).

As with tables, if a view is opened with a different alias, that alias (rather than the name of the view) is passed for the table name parameter in the Open and Close events.

Event

Fired When

AfterCloseTable

After a view is closed.

AfterCreateOffline

After a view is taken offline.

AfterCreateView

After a view is created.

AfterDropOffline

After a view is taken back online.

AfterDropView

After a view is deleted (whether DROP VIEW or DELETE VIEW is used).

AfterModifyView

After a view is modified.

AfterOpenTable

After a view is opened.

AfterRenameView

After a view is renamed.

BeforeCloseTable

Before a view is closed.

BeforeCreateOffline

Before a view is taken offline.

BeforeCreateView

Before a view is created.

BeforeDropOffline

Before a view is taken back online.

BeforeDropView

Before a view is deleted (whether DROP VIEW or DELETE VIEW is used).

BeforeModifyView

Before a view is modified.

BeforeOpenTable

Before a view is opened.

BeforeRenameView

Before a view is renamed.

Database Events for Relations

There are only two types of events for relations: adding and removing. When a relation is modified, it’s first removed and then re-added, so both the drop and add events fire.

Event

Fired When

AfterAddRelation

After a relation is created.

AfterDropRelation

After a relation is removed.

BeforeAddRelation

Before a relation is created.

BeforeDropRelation

Before a relation is removed.

Database Events for Connections

There are four types of events for connections: adding, removing, renaming, and modifying.

Event

Fired When

AfterCreateConnection

After a connection is created.

AfterDeleteConnection

After a connection is removed.

AfterModifyConnection

After a connection is modified.

AfterRenameConnection

After a connection is renamed.

BeforeCreateConnection

Before a connection is created.

BeforeDeleteConnection

Before a connection is removed.

BeforeModifyConnection

Before a connection is modified.

BeforeRenameConnection

Before a connection is renamed.

What Have You Done For Me Lately?

So, what kinds of things can you use Database Events for? Lots of things. See “DBF, FPT, CDX, DBC—Hike!” for some ideas we’ve come up with.

See Also

ALanguage(), Activate Database Event, AfterAddRelation, AfterAddTable, AfterAppendProc, AfterCloseTable, AfterCopyProc, AfterCreateConnection, AfterCreateOffline, AfterCreateTable, AfterCreateView, AfterDBGetProp, AfterDBSetProp, AfterDeleteConnection, AfterDropOffline, AfterDropRelation, AfterDropTable, AfterDropView, AfterModifyConnection, AfterModifyProc, AfterModifyTable, AfterModifyView, AfterOpenTable, AfterRemoveTable, AfterRenameConnection, AfterRenameTable, AfterRenameView, AfterValidateData, BeforeAddRelation, BeforeAddTable, BeforeAppendProc, BeforeCloseTable, BeforeCopyProc, BeforeCreateConnection, BeforeCreateOffline, BeforeCreateTable, BeforeCreateView, BeforeDBGetProp, BeforeDBSetProp, BeforeDeleteConnection, BeforeDropOffline, BeforeDropRelation, BeforeDropTable, BeforeDropView, BeforeModifyConnection, BeforeModifyProc, BeforeModifyTable, BeforeModifyView, BeforeOpenTable, BeforeRemoveTable, BeforeRenameConnection, BeforeRenameTable, BeforeRenameView, BeforeValidateData, CloseData, CursorGetProp(), DBGetProp(), DBSetProp(), Deactivate Database Event, ModifyData, OpenData, PackData